-- EXEC GET_PROCESS_FLOW_FROM_DB 'TFT-284A'

CREATE PROC GET_PROCESS_FLOW_FROM_DB( @BASE_PRODUCT VARCHAR(50) )
AS 
BEGIN
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @IS_PROCESSED INT
	DECLARE @CURR_ROW_IDX INT
	DECLARE @TEMP_TABLE TABLE ( ROW_ID INT IDENTITY(1, 1), PROCESSSTEP_ID INT, NEXT_PROCESSSTEP_ID INT, IS_PROCESSED INT )
	DECLARE @FAB_PROCESS_TABLE TABLE ( PROCESSSTEP_ID INT )
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @ROW_ID INT
	DECLARE @BASE_PRODUCT_ID INT

	SET @IS_PROCESSED = 1
	-- CREATE A TABLE WITH ALL THE FAB PROCESSSTEPS, SINCE WE NEED TO ORDER ALL THE PRODUCT IN THE PRODUCT SETUP		
	--INSERT INTO @FAB_PROCESS_TABLE  SELECT PS.PROCESSSTEP_ID FROM PROCESS_STEP PS, PRODUCT P WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND PS_TYPE = 'FAB' AND P.P_PARENT_PRODUCT_ID = ( SELECT PRODUCT_ID FROM PRODUCT WHERE PART_NUMBER = @BASE_PRODUCT AND P_PARENT_PRODUCT_ID IS NULL )
	SELECT @BASE_PRODUCT_ID = Product_ID FROM PRODUCT WHERE PART_NUMBER = @BASE_PRODUCT AND P_PARENT_PRODUCT_ID IS NULL
	SET @FAB_PROCESSSTEP_ID = 0
	INSERT INTO @FAB_PROCESS_TABLE select distinct ProcessStep_Id from Process_Step_Traverse_table(NOLOCK) where Parent_Product_Id = @BASE_PRODUCT_ID and ProcessStep_Id not in ( select Distinct Next_ProcessStep_Id from Process_Step_traverse_table(NOLOCK))
	SELECT TOP 1 @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @FAB_PROCESS_TABLE
	DELETE FROM @FAB_PROCESS_TABLE WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID

	WHILE @FAB_PROCESSSTEP_ID != 0
	 BEGIN
		SET @PROCESSSTEP_ID = 0
		INSERT INTO @TEMP_TABLE(PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, IS_PROCESSED) SELECT PST.PROCESSSTEP_ID, PST.NEXT_PROCESSSTEP_ID, 1 FROM PROCESS_STEP_TRAVERSE_TABLE PST(NOLOCK) WHERE PST.PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND Parent_Product_Id = @BASE_PRODUCT_ID
		SELECT TOP 1 @PROCESSSTEP_ID = NEXT_PROCESSSTEP_ID, @ROW_ID = ROW_ID FROM @TEMP_TABLE WHERE IS_PROCESSED = 1	
		UPDATE @TEMP_TABLE SET IS_PROCESSED = 0 WHERE ROW_ID = @ROW_ID
		WHILE @PROCESSSTEP_ID != 0
		 BEGIN
			INSERT INTO @TEMP_TABLE(PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, IS_PROCESSED) SELECT PST.PROCESSSTEP_ID, PST.NEXT_PROCESSSTEP_ID, 1 FROM PROCESS_STEP_TRAVERSE_TABLE PST(NOLOCK) WHERE PST.PROCESSSTEP_ID = @PROCESSSTEP_ID AND Parent_Product_Id = @BASE_PRODUCT_ID
			SET @PROCESSSTEP_ID = 0
			SET @ROW_ID = 0
			SELECT TOP 1 @PROCESSSTEP_ID = NEXT_PROCESSSTEP_ID, @ROW_ID = ROW_ID FROM @TEMP_TABLE WHERE IS_PROCESSED = 1	
			UPDATE @TEMP_TABLE SET IS_PROCESSED = 0 WHERE ROW_ID = @ROW_ID
		 END
		SET @FAB_PROCESSSTEP_ID = 0
		SELECT TOP 1 @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @FAB_PROCESS_TABLE
		DELETE FROM @FAB_PROCESS_TABLE WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID		
	 END
	SELECT PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID FROM @TEMP_TABLE
END
